﻿Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections
Imports System
Imports System.IO
Imports System.Text
Imports System.Security.Cryptography
Namespace Servicios
    Public Class usuamanager
        Public Function ap_DescripcionesUsuariosSelMod(ByVal IdUSuario) As DataTable

            Dim cn As New SqlConnection()
            Dim cmd As New SqlCommand()
            Dim da As SqlDataAdapter
            Dim ds As DataSet
            Dim ConnString As String = ConfigurationManager.ConnectionStrings("BDWebCoon").ConnectionString.ToString
            cn.ConnectionString = ConnString
            cmd = New SqlCommand()
            cmd.Connection = cn
            cmd.CommandText = "agua_Cliente_Read"
            'cmd.CommandText = "DescripcionesUsuarios"
            cmd.Parameters.Add("@SysCliente", Data.SqlDbType.Int, 15)
            cmd.Parameters("@SysCliente").Value = IdUSuario

            cmd.CommandType = CommandType.StoredProcedure
            da = New SqlDataAdapter()
            da.SelectCommand = cmd
            ds = New DataSet()
            da.Fill(ds)
            Return ds.Tables(0)

        End Function

        Public Function ap_DescripcionesUsuarios(ByVal condi1 As String, ByVal condi2 As String, ByVal condi3 As String, ByVal condi4 As String, ByVal condi6 As String, ByVal condi7 As String) As DataTable
            Dim sql As String
            Dim cn As New SqlConnection()
            Dim cmd As New SqlCommand()
            Dim da As SqlDataAdapter
            Dim ds As DataSet
            Dim ConnString As String = ConfigurationManager.ConnectionStrings("BDWebCoon").ConnectionString.ToString
            cn.ConnectionString = ConnString
            cmd = New SqlCommand()
            cmd.Connection = cn

            sql = "SELECT     dbo.FC_User.IdUsuario, dbo.FC_User.IdRole, dbo.FC_Role.DesRole, dbo.FC_User.Username, dbo.FC_User.Password, dbo.FC_User.Name, "
            sql = sql & " dbo.FC_UserEntity.IdEntityF, dbo.FC_Entity.CodICP, dbo.FC_Entity.DesEntity"
            sql = sql & " FROM         dbo.FC_User INNER JOIN"
            sql = sql & " dbo.FC_Role ON dbo.FC_User.IdRole = dbo.FC_Role.IdRole INNER JOIN"
            sql = sql & " dbo.FC_UserEntity ON dbo.FC_User.IdUsuario = dbo.FC_UserEntity.IdUsuario INNER JOIN"
            sql = sql & " dbo.FC_Entity ON dbo.FC_UserEntity.IdEntityF = dbo.FC_Entity.IdEntityF"

            'sql = " SELECT dbo.TB_Clientes.SysRolUsuario,  dbo.TB_Clientes.Nombre + ' (Cod:'+dbo.TB_Clientes.Medidor+' - Parcela:'+ dbo.TB_Clientes.Parcela+')' as Nombre, dbo.TB_Clientes.ApellidoPaterno, dbo.TB_Clientes.ApellidoMaterno, dbo.TB_Clientes.Rut, "
            'sql = sql & " dbo.TB_Clientes.Email, dbo.TB_Clientes.Pass, dbo.TB_Clientes.Direccion, dbo.TB_Clientes.Parcela, dbo.TB_Clientes.Medidor, dbo.TB_Clientes.Mantencion, "
            'sql = sql & " dbo.TB_Clientes.CargoFijo, dbo.TB_Clientes.Arriendo, dbo.TB_RolUsuarios.DesRol, dbo.TB_Clientes.Sysid"
            'sql = sql & " FROM dbo.TB_Clientes INNER JOIN"
            'sql = sql & " dbo.TB_RolUsuarios ON dbo.TB_Clientes.SysRolUsuario = dbo.TB_RolUsuarios.Sysid"
            'sql = sql & " WHERE 1=1 " & condi1 & condi2 & condi3 & condi4 & condi6 & condi7

            cmd.CommandText = sql
            cmd.CommandType = CommandType.Text

            da = New SqlDataAdapter()
            da.SelectCommand = cmd
            ds = New DataSet()
            da.Fill(ds)
            Return ds.Tables(0)

        End Function
        Private key() As Byte = {}
        Private IV() As Byte = {&H12, &H34, &H56, &H78, &H90, &HAB, &HCD, &HEF} '

        Public Function Decrypt(ByVal stringToDecrypt As String, _
            ByVal sEncryptionKey As String) As String
            Dim inputByteArray(stringToDecrypt.Length) As Byte
            Try
                key = System.Text.Encoding.UTF8.GetBytes(Left(sEncryptionKey, 8))
                Dim des As New DESCryptoServiceProvider()
                inputByteArray = Convert.FromBase64String(stringToDecrypt)
                Dim ms As New MemoryStream()
                Dim cs As New CryptoStream(ms, des.CreateDecryptor(key, IV), _
                    CryptoStreamMode.Write)
                cs.Write(inputByteArray, 0, inputByteArray.Length)
                cs.FlushFinalBlock()
                Dim encoding As System.Text.Encoding = System.Text.Encoding.UTF8
                Return encoding.GetString(ms.ToArray())
            Catch e As Exception
                Return e.Message
            End Try
        End Function

        Public Function Encrypt(ByVal stringToEncrypt As String, _
       ByVal SEncryptionKey As String) As String
            Try
                key = System.Text.Encoding.UTF8.GetBytes(Left(SEncryptionKey, 8))
                Dim des As New DESCryptoServiceProvider()
                Dim inputByteArray() As Byte = Encoding.UTF8.GetBytes( _
                    stringToEncrypt)
                Dim ms As New MemoryStream()
                Dim cs As New CryptoStream(ms, des.CreateEncryptor(key, IV), _
                    CryptoStreamMode.Write)
                cs.Write(inputByteArray, 0, inputByteArray.Length)
                cs.FlushFinalBlock()
                Return Convert.ToBase64String(ms.ToArray())
            Catch e As Exception
                Return e.Message
            End Try
        End Function
        Public Function CrearUser(ByVal Nombre, ByVal ApellidoPaterno, ByVal Rut, ByVal Email, ByVal TelefonoFijo, ByVal TelefonoMovil, ByVal Pass, ByVal Direccion, ByVal Parcela, ByVal Medidor, ByVal Mantencion, ByVal CargoFijo, ByVal Arriendo, ByVal Password, ByVal Activo) As DataTable
            Dim cn As New SqlConnection()
            Dim cmd As New SqlCommand()
            Dim da As SqlDataAdapter
            Dim ds As DataSet
            Dim ConnString As String = ConfigurationManager.ConnectionStrings("BDWebCoon").ConnectionString.ToString
            cn.ConnectionString = ConnString
            cmd = New SqlCommand()
            cmd.Connection = cn
            cmd.CommandText = "aqua_Cliente_Create"
            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.Add("@Nombre", Data.SqlDbType.VarChar)
            cmd.Parameters("@Nombre").Value = Nombre

            cmd.Parameters.Add("@ApellidoPaterno", Data.SqlDbType.VarChar)
            cmd.Parameters("@ApellidoPaterno").Value = ApellidoPaterno

            cmd.Parameters.Add("@Rut", Data.SqlDbType.VarChar)
            cmd.Parameters("@Rut").Value = Rut

            cmd.Parameters.Add("@Email", Data.SqlDbType.VarChar)
            cmd.Parameters("@Email").Value = Email

            cmd.Parameters.Add("@TelefonoFijo", Data.SqlDbType.VarChar)
            cmd.Parameters("@TelefonoFijo").Value = TelefonoFijo

            cmd.Parameters.Add("@TelefonoMovil", Data.SqlDbType.VarChar)
            cmd.Parameters("@TelefonoMovil").Value = TelefonoMovil

            cmd.Parameters.Add("@Direccion", Data.SqlDbType.VarChar)
            cmd.Parameters("@Direccion").Value = Direccion

            cmd.Parameters.Add("@Parcela", Data.SqlDbType.VarChar)
            cmd.Parameters("@Parcela").Value = Parcela

            cmd.Parameters.Add("@Medidor", Data.SqlDbType.VarChar)
            cmd.Parameters("@Medidor").Value = Medidor

            cmd.Parameters.Add("@Mantencion", Data.SqlDbType.Int)
            If Mantencion.ToString <> "" Then
                cmd.Parameters("@Mantencion").Value = Mantencion
            Else
                cmd.Parameters("@Mantencion").Value = 0
            End If

            cmd.Parameters.Add("@CargoFijo", Data.SqlDbType.Int)
            If CargoFijo.ToString <> "" Then
                cmd.Parameters("@CargoFijo").Value = CargoFijo
            Else
                cmd.Parameters("@CargoFijo").Value = 0
            End If

            cmd.Parameters.Add("@Arriendo", Data.SqlDbType.Int)
            If Arriendo.ToString <> "" Then
                cmd.Parameters("@Arriendo").Value = Arriendo
            Else
                cmd.Parameters("@Arriendo").Value = 0
            End If

            cmd.Parameters.Add("@Password", Data.SqlDbType.VarChar)
            cmd.Parameters("@Password").Value = Password

            cmd.Parameters.Add("@Activo", Data.SqlDbType.Bit)
            cmd.Parameters("@Activo").Value = Activo


            da = New SqlDataAdapter()
            da.SelectCommand = cmd
            ds = New DataSet()
            da.Fill(ds)
        End Function

        Public Function ap_ValidarExistenciaUsuario(ByVal Email) As DataTable

            Dim cn As New SqlConnection()
            Dim cmd As New SqlCommand()
            Dim da As SqlDataAdapter
            Dim ds As DataSet
            Dim ConnString As String = ConfigurationManager.ConnectionStrings("BDWebCoon").ConnectionString.ToString
            cn.ConnectionString = ConnString
            cmd = New SqlCommand()
            cmd.Connection = cn
            cmd.CommandText = "AP_ValidarExistenciaUsuario"

            cmd.Parameters.Add("@Email", Data.SqlDbType.VarChar)
            cmd.Parameters("@Email").Value = Email

            cmd.CommandType = CommandType.StoredProcedure
            da = New SqlDataAdapter()
            da.SelectCommand = cmd
            ds = New DataSet()
            da.Fill(ds)
            Return ds.Tables(0)

        End Function
        Public Function PassAleatoria(ByVal IDClave As Integer) 'As DataTable
            Dim cn As New SqlConnection()
            Dim cmd As New SqlCommand()
            Dim da As SqlDataAdapter
            Dim ds As DataSet
            Dim ConnString As String = ConfigurationManager.ConnectionStrings("BDWebCoon").ConnectionString.ToString
            cn.ConnectionString = ConnString

            cmd = New SqlCommand()
            cmd.Connection = cn
            cmd.CommandText = "AP_clavealeatoria"
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add("@IDSys", Data.SqlDbType.Int)
            cmd.Parameters("@IDSys").Value = IDClave

            da = New SqlDataAdapter()
            da.SelectCommand = cmd
            ds = New DataSet()
            da.Fill(ds)

            Return ds.Tables(0).Rows(0).Item(0).ToString
        End Function
        Public Function ModUser(ByVal SysCliente, ByVal Nombre, ByVal ApellidoPaterno, ByVal Rut, ByVal Email, ByVal TelefonoFijo, ByVal TelefonoMovil, ByVal Pass, ByVal Direccion, ByVal Parcela, ByVal Medidor, ByVal Mantencion, ByVal CargoFijo, ByVal Arriendo, ByVal Activo) As DataTable
            Dim cn As New SqlConnection()
            Dim cmd As New SqlCommand()
            Dim da As SqlDataAdapter
            Dim ds As DataSet
            Dim ConnString As String = ConfigurationManager.ConnectionStrings("BDWebCoon").ConnectionString.ToString
            cn.ConnectionString = ConnString
            cmd = New SqlCommand()
            cmd.Connection = cn
            cmd.CommandText = "aqua_Cliente_Update"
            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.Add("@SysCliente", Data.SqlDbType.Int)
            cmd.Parameters("@SysCliente").Value = SysCliente

            cmd.Parameters.Add("@Nombre", Data.SqlDbType.VarChar)
            cmd.Parameters("@Nombre").Value = Nombre

            cmd.Parameters.Add("@ApellidoPaterno", Data.SqlDbType.VarChar)
            cmd.Parameters("@ApellidoPaterno").Value = ApellidoPaterno

            cmd.Parameters.Add("@Rut", Data.SqlDbType.VarChar)
            cmd.Parameters("@Rut").Value = Rut

            cmd.Parameters.Add("@Email", Data.SqlDbType.VarChar)
            cmd.Parameters("@Email").Value = Email

            cmd.Parameters.Add("@TelefonoFijo", Data.SqlDbType.VarChar)
            cmd.Parameters("@TelefonoFijo").Value = TelefonoFijo

            cmd.Parameters.Add("@TelefonoMovil", Data.SqlDbType.VarChar)
            cmd.Parameters("@TelefonoMovil").Value = TelefonoMovil

            cmd.Parameters.Add("@Direccion", Data.SqlDbType.VarChar)
            cmd.Parameters("@Direccion").Value = Direccion

            cmd.Parameters.Add("@Parcela", Data.SqlDbType.VarChar)
            cmd.Parameters("@Parcela").Value = Parcela

            cmd.Parameters.Add("@Medidor", Data.SqlDbType.VarChar)
            cmd.Parameters("@Medidor").Value = Medidor

            cmd.Parameters.Add("@Mantencion", Data.SqlDbType.Int)
            If Mantencion.ToString <> "" Then
                cmd.Parameters("@Mantencion").Value = Mantencion
            Else
                cmd.Parameters("@Mantencion").Value = 0
            End If

            cmd.Parameters.Add("@CargoFijo", Data.SqlDbType.Int)
            If CargoFijo.ToString <> "" Then
                cmd.Parameters("@CargoFijo").Value = CargoFijo
            Else
                cmd.Parameters("@CargoFijo").Value = 0
            End If

            cmd.Parameters.Add("@Arriendo", Data.SqlDbType.Int)
            If CargoFijo.ToString <> "" Then
                cmd.Parameters("@Arriendo").Value = Arriendo
            Else
                cmd.Parameters("@Arriendo").Value = 0
            End If

            cmd.Parameters.Add("@Activo", Data.SqlDbType.Bit)
            cmd.Parameters("@Activo").Value = Activo

            da = New SqlDataAdapter()
            da.SelectCommand = cmd
            ds = New DataSet()
            da.Fill(ds)
        End Function
        Public Function FormatPass(ByVal idUsuario, ByVal PAssw) As DataTable
            Dim cn As New SqlConnection()
            Dim cmd As New SqlCommand()
            Dim da As SqlDataAdapter
            Dim ds As DataSet
            Dim ConnString As String = ConfigurationManager.ConnectionStrings("BDWebCoon").ConnectionString.ToString
            cn.ConnectionString = ConnString
            cmd = New SqlCommand()
            cmd.Connection = cn
            cmd.CommandText = "aqua_Cliente_Update_Newpass"
            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.Add("@Sysid", Data.SqlDbType.VarChar)
            cmd.Parameters("@Sysid").Value = idUsuario

            cmd.Parameters.Add("@NewPass", Data.SqlDbType.VarChar)
            cmd.Parameters("@NewPass").Value = PAssw


            da = New SqlDataAdapter()
            da.SelectCommand = cmd
            ds = New DataSet()
            da.Fill(ds)
        End Function
        'Private key() As Byte = {}
        'Private IV() As Byte = {&H12, &H34, &H56, &H78, &H90, &HAB, &HCD, &HEF} '

        'Public Function Decrypt(ByVal stringToDecrypt Encrypt String, _
        '    ByVal sEncryptionKey As String) As String
        '    Dim inputByteArray(stringToDecrypt.Length) As Byte
        '    Try
        '        key = System.Text.Encoding.UTF8.GetBytes(Left(sEncryptionKey, 8))
        '        Dim des As New DESCryptoServiceProvider()
        '        inputByteArray = Convert.FromBase64String(stringToDecrypt)
        '        Dim ms As New MemoryStream()
        '        Dim cs As New CryptoStream(ms, des.CreateDecryptor(key, IV), _
        '            CryptoStreamMode.Write)
        '        cs.Write(inputByteArray, 0, inputByteArray.Length)
        '        cs.FlushFinalBlock()
        '        Dim encoding As System.Text.Encoding = System.Text.Encoding.UTF8
        '        Return encoding.GetString(ms.ToArray())
        '    Catch e As Exception
        '        Return e.Message
        '    End Try
        'End Function
    End Class
End Namespace

